Imports MyClasses
Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration

Public Class DSale
    Dim objLocSale As CSale
    Dim intSaleID As Integer = 0
    Public SeqSaleID As String = 0
    Public intBlankID As Integer = 0
    Public Function Save(ByRef ObjSale As CSale, ByRef objsaleItem() As CNGItem, ByRef dtbOtherCharges As DataTable, ByVal dtbTaxes As DataTable, ByVal intVoucherID As Integer, Optional ByVal IsApprovalUpdate As Boolean = False) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0

        Try
            objLocSale = ObjSale
            dbConnection.BeginTransaction()
            If intVoucherID > 0 Then
                intSaleID = intVoucherID
                UpdateSale(ObjSale, dbConnection, intVoucherID)
                ' SeqSaleID = ObjSale.intSRV_Number
            Else
                intSaleID = SaveSale(ObjSale, dbConnection)
            End If
            If intSaleID > 0 Then
                If intVoucherID > 0 Then
                    If UpdateSaleItemDetails(intVoucherID, dbConnection) = 0 Then
                        intResult = 0
                        Exit Function
                    End If
                    '
                    intResult = dbConnection.ExecuteNonQuery("DELETE FROM tSaleDet WHERE SaleID=" & intSaleID.ToString())
                End If
                If IsApprovalUpdate Then
                    UpdateChallanItemDetail(objsaleItem, dtbOtherCharges, dtbTaxes, dbConnection)
                End If
                objLocSale.SaleBillID = intSaleID
                intResult = SaveSaleItemDetail(intSaleID, objsaleItem, dbConnection)

            End If
            If intResult = 0 Then Exit Function
            If funPriSaveTaxes(dtbTaxes, dbConnection) = 0 Then
                intResult = 0
                Exit Function
            End If
            If SaveOtherCharges(dtbOtherCharges, dbConnection) = 0 Then
                intResult = 0
                Exit Function
            End If

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "Save")
            intResult = 0
        Finally
            If intResult = 0 Then
                intSaleID = 0
                dbConnection.RollBackTransaction()
            Else
                dbConnection.CommitTransaction()
            End If
            dbConnection.closeSqlcon()
            If intSaleID = 0 Then
                Save = 0
            Else
                Save = ObjSale.intSRV_Number
            End If
            'Save = intSaleID
        End Try
    End Function
    Public Function UpdateChallanItemDetail(ByRef objsaleItem() As CNGItem, dtbOtherCharges As DataTable, dtbTax As DataTable, ByRef dbConnection As dbAccess) As Integer
        Try
            Dim StrItemsUpdates As String = ","
            For Each Item As CNGItem In objsaleItem
                If DGeneral.AddNGItemStock(Item, dbConnection) = 0 Then '' UPDATE STOCK 
                    Return 0
                End If

                If dbConnection.ExecuteNonQuery("DELETE FROM tSaleDet WHERE SaleID=" & Item.iBillID.ToString() & "  AND ItemID= " & Item.ItemID & "   AND PackUpdateID=" & Item.intPackUpdateDtl_PK) = 0 Then 'REMOVE ENTRY FROM existing sale
                    Return 0
                End If
                If (StrItemsUpdates.IndexOf("," & Item.iBillID.ToString() & ",") <= 0) Then
                    StrItemsUpdates = StrItemsUpdates & Item.iBillID.ToString() & ","
                End If

            Next
            'Dim StrItemsUpdates As String = ","
            Dim SaleIDs() As String
            SaleIDs = StrItemsUpdates.Split(",")
            Dim strSql As String
            For iCount As Integer = 0 To SaleIDs.Length - 1
                If SaleIDs(iCount) <> "" Then
                    If (dbConnection.ExecuteScalar("SELECT COUNT(SALEID) FROM tSaleDet WHERE SaleID=" & SaleIDs(iCount)) > 0) Then


                        'UPdate TAXES
                        For Each taxRow As DataRow In dtbTax.Rows
                            strSql = "Update tSaleTax SET TaxableAmt =(TaxAmt / TAX *100) , TaxAmt=  (SELECT SUM (TaxAmt) FROM tSaleDEt WHERE SaleID =" & SaleIDs(iCount) & " AND TaxID=" & taxRow.Item(0) & ") WHERE SaleID = " & SaleIDs(iCount) & " AND TaxID=" & taxRow.Item(0) & ""
                            If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                                'Return 0
                            End If
                        Next
                        strSql = "UPDATE TSale SET TotalTaxes = (SELECT SUM(TaxAmt)FROM  tSaleTax WHERE SaleID=" & SaleIDs(iCount) & "  ) ,Discount = (SELECT SUM(DISC_AMT)FROM  tSaleDEt WHERE SaleID=" & SaleIDs(iCount) & "  ) ,Amount = (SELECT SUM(Amount)FROM  tSaleDEt WHERE SaleID=" & SaleIDs(iCount) & "  ) WHERE SaleID= " & SaleIDs(iCount)
                        If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                            Return 0

                        End If
                        'update otherCharges
                        'For Each drCharge As DataRow In dtbOtherCharges.Rows
                        '    '(SaleID,OtherChargeID,Value,Amount)
                        '    'strSql = "UPDATE tSaleOtherCharges SET Amount) VALUES(" & intSaleID.ToString & "," & drCharge.Item(0).ToString & ",'" & drCharge.Item(2).ToString & "'," & drCharge.Item(3).ToString & ") "
                        '    If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                        '        Return 0

                        '    End If
                        'Next

                        'strSql = "UPDATE TSale SET OtherCharges = (SELECT SUM(Amount)FROM  tSaleOtherCharges WHERE SaleID=" & SaleIDs(iCount) & " ) ,GrossAmount = Amount + OtherCharges  WHERE SaleID= " & SaleIDs(iCount) & ""
                    Else '' All Records of Sale are converted so remove entry.
                        If dbConnection.ExecuteNonQuery("DELETE FROM  tSaleTax  WHERE SaleID=" & SaleIDs(iCount)) = 0 Then
                            Return 0
                        End If
                        If dbConnection.ExecuteNonQuery("DELETE FROM  tSaleOtherCharges  WHERE SaleID=" & SaleIDs(iCount)) = 0 Then
                            Return 0
                        End If
                        If dbConnection.ExecuteNonQuery("DELETE FROM  tSale  WHERE SaleID=" & SaleIDs(iCount)) = 0 Then
                            Return 0
                        End If
                    End If
                End If
            Next
        Catch ex As Exception

        End Try
    End Function
    Public Sub updateBilledStatus()
        DbHelper.ExecuteNonQuery("UPDATE tSale SET Billed=1 WHERE SaleID=" & intSaleID.ToString)
    End Sub
    Public Sub updateChallanStatus(chrParty As String)
        If chrParty = "C" Then
            DbHelper.ExecuteNonQuery("UPDATE tSale SET Billed=1 WHERE SaleID=" & intSaleID.ToString)
        Else
            DbHelper.ExecuteNonQuery("UPDATE tSale SET Billed=2 WHERE SaleID=" & intSaleID.ToString)
        End If
    End Sub

    Private Function funPriSaveTaxes(dtbTax As DataTable, ByRef dbConnection As dbAccess) As Int16
        dbConnection.ExecuteNonQuery("DELETE FROM tSaleTax WHERE SaleID=" & intSaleID.ToString())
        Dim param(4) As SqlParameter
        Dim strSql As String = "INSERT INTO tSaleTax(SaleID,TaxID,TAX,TaxableAmt,TaxAmt)" & _
                                              "VALUES(@SaleID,@TaxID,@TAX,@TaxableAmt,@TaxAmt)"
        Dim intResult As Integer
        For Each taxRow As DataRow In dtbTax.Rows
            If taxRow.Item(2) > 0 Then

            param(0) = New SqlParameter("@SaleID", intSaleID)
            param(1) = New SqlParameter("@TaxID", taxRow.Item(0))
            param(2) = New SqlParameter("@TAX", taxRow.Item(1))
            param(3) = New SqlParameter("@TaxableAmt", taxRow.Item(2))
            param(4) = New SqlParameter("@TaxAmt", taxRow.Item(3))
                intResult += dbConnection.ExecuteNonQuery(strSql, param)
            End If

        Next
        Return intResult
    End Function
    Public Function SaveSale(ByRef ObjSale As CSale, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String = ""
        Dim param(19) As SqlParameter
        Try
            strSql = "INSERT INTO tSale(SeqSaleID,PartyCode,SaleDate,PromoterID,SiteID,Amount,OtherCharges,TotalTaxes,Discount,GrossAmount,ShipingAddress,Status,CU,CD,LU,LD,FinYrID,SaleType,PriceListID,Billed)" & _
                     "VALUES(@SeqSaleID,@PartyCode,@SaleDate,@PromoterID,@SiteID,@Amount,@OtherCharges,@TotalTaxes,@Discount,@GrossAmount,@ShipingAddress,@Status,@CU,@CD,@LU,@LD,@FinYrID,@SaleType,@PriceListID,@Billed)"
            ''''''''''''''''ShipingAddress,Status,CU,CD,LU,LD,FinYrID,SaleType
            If (intBlankID = 0) Then
                SeqSaleID = DGeneral.GetSeqNum(IIf(ObjSale.SaleType = DGeneral.DocTypes.INVOICE Or ObjSale.SaleType = DGeneral.DocTypes.APPROVAL_UPDATE, DGeneral.DocTypes.CHALLAN, ObjSale.SaleType), dbConnection, True)
            Else
                dbConnection.ExecuteNonQuery("UPDATE T_BlankEntry SET IsUsed= 1 WHERE SeqNo=" & intBlankID.ToString())
                'SeqID is passed when approval update and challan update
            End If

            ObjSale.intSRV_Number = SeqSaleID
            param(0) = New SqlParameter("@SeqSaleID", SeqSaleID)
            param(1) = New SqlParameter("@PartyCode", ObjSale.PartyID)
            param(2) = New SqlParameter("@SaleDate", ObjSale.SaleDate)
            param(3) = New SqlParameter("@PromoterID", ObjSale.PromoterID)
            param(4) = New SqlParameter("@SiteID", ObjSale.Site)
            param(5) = New SqlParameter("@Amount", ObjSale.Amount)
            param(6) = New SqlParameter("@OtherCharges", ObjSale.dblOtherCharges)
            param(7) = New SqlParameter("@TotalTaxes", ObjSale.TotalTax)
            param(8) = New SqlParameter("@Discount", ObjSale.Discount)
            param(9) = New SqlParameter("@GrossAmount", ObjSale.GrossAmt)
            param(10) = New SqlParameter("@ShipingAddress", ObjSale.ShippingAdress)
            param(11) = New SqlParameter("@Status", "1") 'ACTIVE
            param(12) = New SqlParameter("@CU", CCommon.GetUserID)
            param(13) = New SqlParameter("@CD", Date.Now.Date())
            param(14) = New SqlParameter("@LU", CCommon.GetUserID)
            param(15) = New SqlParameter("@LD", Date.Now.Date())
            param(16) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
            param(17) = New SqlParameter("@SaleType", IIf(ObjSale.SaleType = DGeneral.DocTypes.APPROVAL_UPDATE, DGeneral.DocTypes.INVOICE, ObjSale.SaleType))
            param(18) = New SqlParameter("@PriceListID", ObjSale.priceListID)
            param(19) = New SqlParameter("@Billed", 0)
            If dbConnection.ExecuteNonQuery(strSql, param) > 0 Then

                Return dbConnection.ExecuteScalar("SELECT TOP 1 SaleID FROM tSale ORDER BY SaleID DESC")
            Else
                Return 0
            End If


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSale")
            Return 0
        End Try

    End Function
    Public Shared Function UpdateSale(ByRef ObjSale As CSale, ByRef dbConnection As dbAccess, ByVal intVoucherID As Integer) As Integer

        Dim strSql As String = ""
        Dim param(13) As SqlParameter
        Try
            strSql = "UPDATE tSale SET PartyCode=@PartyCode,PromoterID=@PromoterID,SiteID=@SiteID,Amount=@Amount,OtherCharges=@OtherCharges,TotalTaxes=@TotalTaxes,Discount=@Discount,GrossAmount=@GrossAmount,ShipingAddress=@ShipingAddress,LU=@LU,LD=@LD,PriceListID=@PriceListID,SaleType=@SaleType WHERE SaleID=@SaleID"

            param(0) = New SqlParameter("@PartyCode", ObjSale.PartyID)
            param(1) = New SqlParameter("@PromoterID", ObjSale.PromoterID)
            param(2) = New SqlParameter("@SiteID", ObjSale.Site)
            param(3) = New SqlParameter("@Amount", ObjSale.Amount)
            param(4) = New SqlParameter("@OtherCharges", ObjSale.dblOtherCharges)
            param(5) = New SqlParameter("@TotalTaxes", ObjSale.TotalTax)
            param(6) = New SqlParameter("@Discount", ObjSale.Discount)
            param(7) = New SqlParameter("@GrossAmount", ObjSale.GrossAmt)
            param(8) = New SqlParameter("@ShipingAddress", ObjSale.ShippingAdress)
            param(9) = New SqlParameter("@SaleType", IIf(ObjSale.blnConvertToInvoice, System.Convert.ToInt16(DGeneral.DocTypes.INVOICE), ObjSale.SaleType))            '
            param(10) = New SqlParameter("@LU", CCommon.GetUserID)
            param(11) = New SqlParameter("@LD", Date.Now.Date())
            param(12) = New SqlParameter("@PriceListID", ObjSale.priceListID)
            param(13) = New SqlParameter("@SaleID", intVoucherID)

            Return dbConnection.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSale")
            Return 0
        End Try

    End Function

    Public Function UpdateSaleItemDetails(ByVal SaleID As Integer, ByRef dbConnection As dbAccess) As Integer

        Dim dtDItem As DataTable = GetSaleBillItems(SaleID)
        Dim objNgItem As CNGItem = New CNGItem
        Dim i As Integer = 0
        Try
            For Each SaleItem As DataRow In dtDItem.Rows
                If objLocSale.SaleType <> DGeneral.DocTypes.QUOTATION Then
                    objNgItem.isTinTin = False ' SaleItem.Item("IsTinTin")
                    objNgItem.Quantity = SaleItem.Item("Qty")
                    objNgItem.intPackUpdateDtl_PK = SaleItem.Item("PackUpdateIDPK")
                    If DGeneral.AddNGItemStock(objNgItem, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
                i += 1
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleItemDetail", "")
            Return 0
        End Try
    End Function
    Public Function SaveSaleItemDetail(ByVal SaleID As Integer, ByRef objsaleItem() As CNGItem, ByRef dbConnection As dbAccess) As Integer


        Dim strSql As String = String.Empty
        Dim param(12) As SqlParameter

        Dim intRecordsUpdate As Integer
        Dim i As Integer
        Try
            For Each Item As CNGItem In objsaleItem
                If IsNothing(Item) Then
                    Exit For
                End If
                strSql = " INSERT INTO tSaleDet(SaleID,ItemID,CLRcode,PackUpdateID,SalePrice,Qty,Amount,DISC,DISC_AMT,TAXID,TAXAMT,SchemeDiscount,FinYrId)" & _
                         " VALUES(@SaleID,@ItemID,@CLRcode,@PackUpdateID,@SalePrice,@Qty,@Amount,@DISC,@DISC_AMT,@TAXID,@TAXAMT,@SchemeDiscount,@FinYrId)"

                param(0) = New SqlParameter("@SaleID", SaleID)
                param(1) = New SqlParameter("@ItemID", Item.ItemID)
                param(2) = New SqlParameter("@CLRcode", Item.ColorCode)
                param(3) = New SqlParameter("@PackUpdateID", Item.intPackUpdateDtl_PK)
                param(4) = New SqlParameter("@SalePrice", Item.dblRate)
                param(5) = New SqlParameter("@Qty", Item.Quantity)
                param(6) = New SqlParameter("@Amount", Item.Amount)
                param(7) = New SqlParameter("@DISC", Item.strDisc)
                param(8) = New SqlParameter("@DISC_AMT", Item.dblDISCAmt)
                param(9) = New SqlParameter("@TAXID", Item.TaxID)
                param(10) = New SqlParameter("@TAXAMT", Item.TaxAmt)
                param(11) = New SqlParameter("@SchemeDiscount", Item.SchemeDiscount)
                param(12) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                i = dbConnection.ExecuteNonQuery(strSql, param)
                If i = 0 Then
                    Return i
                End If

                If objLocSale.SaleType <> DGeneral.DocTypes.QUOTATION Then
                    If DGeneral.SubstractNGItemStock(Item, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
                intRecordsUpdate += i
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleItemDetail", strSql)
            Return 0
        End Try
    End Function

    Private Function SaveOtherCharges(ByRef dtbOtherCharges As DataTable, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = "DELETE FROM tSaleOtherCharges WHERE SaleID=" + intSaleID.ToString()
        If dbConnection.ExecuteNonQuery(strSql) = 0 Then

        End If
        For Each drCharge As DataRow In dtbOtherCharges.Rows

            strSql = "INSERT INTO tSaleOtherCharges(SaleID,OtherChargeID,Value,Amount) VALUES(" & intSaleID.ToString & "," & drCharge.Item(0).ToString & ",'" & drCharge.Item(2).ToString & "'," & drCharge.Item(3).ToString & ") "
            If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                Return 0
            End If
        Next
        Return 1
    End Function


    Public Shared Function SaveSaleTaxDetails(ByVal BillID As Long, ByVal ItemID As Long, ByVal CLRcode As String, ByVal Amount As Double, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim param(8) As SqlParameter
        Dim intResult As Integer
        Try
            Dim dtDItemTaxes As New DataTable ' = dtDItemTaxes(
            dtDItemTaxes = DTax.GetItemTaxes(ItemID, CLRcode, Amount)
            If IsNothing(dtDItemTaxes) Then Return 1
            For Each Tax As DataRow In dtDItemTaxes.Rows

                strSql = " INSERT INTO tBillItemTax(BillItemSeqid,ItemId,BillID,BillType,CLRcode, " & _
                                     " FormulaId,TaxID,TaxPercent,TaxAmount) " & _
                                     " VALUES (@BillItemSeq,@ItemID,@BillID,@BillType,@CLRcode,@FormulaId,@TaxID,@TaxPerc,@TaxAmount)"

                param(0) = New SqlParameter("@BillItemSeq", DGeneral.GetSeqNum("BillItemTax", dbConnection, True))
                param(1) = New SqlParameter("@ItemID", ItemID)
                param(2) = New SqlParameter("@BillID", BillID)
                param(3) = New SqlParameter("@BillType", "S")
                param(4) = New SqlParameter("@CLRcode", CLRcode)
                param(5) = New SqlParameter("@FormulaID", "")
                param(6) = New SqlParameter("@TaxID", Tax("ID"))
                param(7) = New SqlParameter("@TaxPerc", Tax("Val"))
                param(8) = New SqlParameter("@TaxAmount", Tax("Amount"))
                intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
            Next

            Return intResult

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleTaxDetails", strSql)
            Return 0
        End Try
    End Function



    Public Shared Function SaveSaleReturnItems(ByVal ReturnID As Integer, ByRef Items() As CReturnItem, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String
        Dim param(7) As SqlParameter
        Dim i As Integer

        Try

            For Each Item As CReturnItem In Items
                If Not IsNothing(Item) Then
                    strSql = " INSERT INTO tSaleReturnDet(SaleReturnID,ItemID,CLRcode,Qty,ReturnPrice,Amount,Reason,FinYrId)" & _
                                              " VALUES(@SaleReturnID,@ItemID,@CLRcode,@Qty,@ReturnPrice,@Amount,@Reason,@FinYrId)"

                    param(0) = New SqlParameter("@SaleReturnID", ReturnID)
                    param(1) = New SqlParameter("@ItemID", Item.ItemID)
                    param(2) = New SqlParameter("@CLRcode", Item.CLRcode)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@ReturnPrice", Item.Rate)
                    param(5) = New SqlParameter("@Amount", Item.Amount)
                    'param(6) = New SqlParameter("@Reason", Item.Reason)
                    'Modified By:-(2008/01/16)
                    param(6) = New SqlParameter("@Reason", IIf(IsNothing(Item.Reason), "", Item.Reason))
                    param(7) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                    i = i + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return i

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleReturnItems")
            Return 0
        End Try
    End Function

#Region "GETDETAILS"
    Public Shared Function GetSaleBillList() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT SaleID,SeqSaleID AS VoucherNo,SaleDate,Amount,mMiscCd.CodeDesc +' '+ FName +' '+MName +' '+LName AS Name ," & _
                     " mCustomer.PartyCode FROM tSale, mPerson, mCustomer, mmisccd " & _
                     " WHERE  tSale.PartyCode <>0 AND tSale.status ='A' AND mPerson.PersonID=mCustomer.PersonID" & _
                     " AND tSale.PartyCode=mCustomer.PartyCode AND mMiscCd.CodeType='TITLE' AND mMiscCd.CodeID=mPerson.Title" & _
                     " UNION " & _
                     " SELECT SaleID,SeqSaleID AS VoucherNo,SaleDate,Amount," & _
                     " title +' '+ first_name +' '+middle_name +' '+last_name AS Name ,mCustomer.PartyCode " & _
                     " FROM tSale, mPatient, mCustomer WHERE  tSale.PartyCode <>0 AND tSale.status ='A'  AND patient_id=mCustomer.PatientID " & _
                     " AND tSale.PartyCode=mCustomer.PartyCode "

            
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "ClearCounterItem", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleBill(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.SeqSaleID,S.PartyCode AS PartyID ,ISNULL(P.PartyCode,'') AS PartyCode,ISNULL(P.PartyName,'') AS PartyName, S.SaleDate,S.PromoterID,ISNULL(R.PromoterName,'') AS PromoterName, ISNULL(R.PromoterCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.TotalTaxes,S.Discount,S.GrossAmount,S.ShipingAddress,S.SaleType,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode  FROM tSale S LEFT OUTER JOIN Party P ON(S.PartyCode = P.ID) LEFT OUTER JOIN Promoter R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleBill", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetReturnSaleBill(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.SeqSaleID,S.PartyCode AS PartyID ,ISNULL(P.PartyCode,'') AS PartyCode,ISNULL(P.PartyName,'') AS PartyName, S.SaleDate,S.PromoterID,ISNULL(R.PromoterName,'') AS PromoterName, ISNULL(R.PromoterCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.TotalTaxes,S.Discount,S.GrossAmount,S.ShipingAddress,S.SaleType,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode  FROM tSale S LEFT OUTER JOIN Party P ON(S.PartyCode = P.ID) LEFT OUTER JOIN Promoter R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleBill", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK ,D.GodownId,D.PackingID,D.PackSizeID,S.SalePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName,PK.packSize + ' ' + PK.PackingDescription AS PCK FROM tSaleDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleBillReturnItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try

            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK ,D.GodownId,D.PackingID,D.PackSizeID,S.SalePrice,S.Qty - S.RetQty AS Qty ,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName,PK.packSize + ' ' + PK.PackingDescription AS PCK FROM tSaleDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.Qty > S.RetQty AND S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleTaxes(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.TaxID,S.TAX,S.TaxableAmt,S.TaxAmt, TaxAmt+ TaxableAmt AS TOTAL FROM tSaleTax S WHERE S.SaleID=" & intSearchID.ToString() & " UNION SELECT Id AS TAXID,TaxPercentage AS TAX,'0' AS TAXABLEAMT,'0' AS TAXAMT,'0' AS TOTAL FROM TAX WHERE IsDeleted=0 AND ID NOT IN(SELECT DISTINCT S.TaxID FROM  tSaleTax S WHERE S.SaleID=" & intSearchID.ToString() & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleTaxes", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetSaleBillOtherCharges(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT O.ID,O.Chargename,ISNULL(S.Value,0)AS Value,ISNULL(S.Amount,0) AS Amount,O.IsTaxable FROM tSaleOtherCharges S RIGHT OUTER JOIN OtherCharge O ON (S.OtherChargeID=O.ID) WHERE O.IsDeleted =0 AND O.AddToInvoice =1 AND S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "ClearCounterItem", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetItemUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As String
        Try
            Return DbHelper.ExecuteScalar("SELECT SellUnit FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetItemUnit")
            Return ""
        End Try
    End Function

    Public Shared Function GetItemPackUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT SellPack,SellUnit,ExpDate,ConversionFactor FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetItemPackUnit")
            Return Nothing
        End Try
    End Function

    'Public Shared Function GetCounterData(ByVal CounterID As Integer) As DataTable
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT SeqID,ItemID,CLRcode,SaleUnit,SalePrice,Qty,Amount,SchemeID,schemeItemQty,SchemeDiscount,FreeReplacement,StockUpdate,Status FROM wSaleDet WHERE CounterID=" & CounterID & " Order by SeqID,Status"
    '        Return DbHelper.ExecuteDataTable(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterData", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer, ByRef dbConnection As dbAccess) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return dbConnection.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItemDetail(ByVal WorkingSeqID As Integer) As String
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItemDetail", StrSql)
            Return ""
        End Try
    End Function

    Public Shared Function GetWItemsDetails(ByVal WorkingSeqID As String) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT ItemID ,CLRcode,Qty ,SchemeID,schemeItemQty,VendorID,StockID FROM wSaleDet WHERE SeqID IN(" & WorkingSeqID & ")"
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItemsDetails", StrSql)
            Return Nothing
        End Try
    End Function

    ' 3/12/2007
    'Public Shared Function GetCounterDetails() As DataTable

    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT mCounter.CounterID,COUNT(ItemID) AS Items,SUM(amount)AS Amount  " & _
    '                "FROM wSaleDet INNER JOIN mCounter ON mCounter.CounterID=wSaleDet.CounterID  " & _
    '                "WHERE wSaleDet.status IN ('W','U') AND mCounter.IsVisibile=1 GROUP BY mCounter.CounterID "
    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterDetails", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterSummary() As DataTable

    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT M.CounterID AS Counter,(Select SUM(Amt) FROM    " & _
    '                 "(Select CounterID,SUM(AMOUNT) amt FROM tSale WHERE PartyCode=0 AND  " & _
    '                 "DATEDIFF(D,CD,GETDATE())=0  AND CounterID=M.CounterID AND FinalBillID<>'F' GROUP BY CounterID " & _
    '                 "UNION " & _
    '                 "SELECT CounterID,SUM(Amount) amt FROM wsaledet where Status IN ('W','F','U') AND " & _
    '                 "DATEDIFF(D,CD,GETDATE())=0  AND CounterID=M.CounterID GROUP BY CounterID) X Group by CounterID " & _
    '                 ") as 'Counter Sale', " & _
    '                 "(SELECT SUM(S.Amount) FROM tSale S WHERE " & _
    '                 "DATEDIFF(D,S.SaleDate,GETDATE())=0 AND CounterID=M.CounterID AND CUSTID>0 AND FinalBillID<>'F' " & _
    '                 "GROUP BY CounterID) AS 'Regular Sale',(SELECT COUNT(S.SaleID) " & _
    '                 "FROM tSale S where DATEDIFF(D,S.SaleDate,GETDATE())=0 AND CounterID=M.CounterID AND CUSTID>0 AND FinalBillID<>'F' " & _
    '                 "GROUP BY CounterID) AS 'Total Bills' FROM MCOUNTER M"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterSummary", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterUpdates(ByVal intCounterID) As DataTable
    '    'Returns only Counters with no OF items & Amount of Perticular Counter
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT Sum(W.Amount) AS 'Amount',count(w.CounterID)as 'Items'" & _
    '                 "FROM wSaleDet W WHERE " & _
    '                 "W.Status IN ('W','U')  AND  W.CounterID=" & intCounterID & _
    '                 " GROUP BY W.CounterID ORDER BY W.CounterID"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterSummary")
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetItemBatchVendors(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
    '    'Returns only Counters with no OF items & Amount of Perticular Counter
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT VendorID,Quantity FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & " '"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterSummary")
    '        strSql = Nothing
    '        Return Nothing
    '    End Try

    'End Function
    'Written By:- (20071023)    'Selecting Search Record
    Public Shared Function GetSearchItems(ByVal SellRecNo As Integer) As DataTable
        Dim StrSql As String
        Try
            StrSql = "SELECT sdet.SaleReturnID,sdet.ItemID,sdet.CLRcode,sdet.Qty,sdet.ReturnPrice,sdet.Amount,sdet.Reason," & _
                     " mp.seqitemid,mp.itemname, " & _
                     " tsr.SaleID,tsr.SaleReturnDate,tsr.Amount,tsr.Remark, " & _
                     " sdet.ItemID, sdet.CLRcode" & _
                     " FROM tSaleReturnDet sdet,tSaleReturn tsr,mpitem mp " & _
                     " WHERE mp.itemid=sdet.itemid AND tsr.SaleReturnID=sdet.SaleReturnID AND " & _
                     " sdet.SaleReturnID=" & SellRecNo

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSearchItems")
            Return Nothing
        End Try
    End Function

    'Written By:- (20071029)    'Select Holiday Date    ' Query Modified By  
    'Public Shared Function IsHoliday() As Integer
    '    Dim StrSql As String
    '    Try
    '        'StrSql = "SELECT COUNT(*) FROM mholidays WHERE holidaydate='" & CurDate & "'"
    '        StrSql = "SELECT COUNT(*) FROM mHolidays WHERE DateDiff (D,getdate(),Holidaydate) =0"
    '        Return DbHelper.ExecuteScalar(StrSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "IsHoliday")
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterAmount(ByVal CounterNo As Byte) As Double
    '    Try
    '        Dim strSql As String = "SELECT SUM (Amount) as Amount FROM wSaleDet WHERE CounterID=" & CounterNo
    '        Return DbHelper.ExecuteScalar(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterAmount")
    '        Return 0
    '    End Try
    'End Function
    'Public Shared Function CheckForSchedule(ByVal intCounters As Byte) As Integer
    '    Try
    '        Dim strSql As String = "SELECT I.ScheduleDrug,warnMessage " & _
    '                               "FROM wSaleDet W INNER JOIN mpItem I ON W.ItemID=I.ItemID " & _
    '                               "LEFT OUTER JOIN mWarning ON  mWarning.warnValue=I.ScheduleDrug " & _
    '                               "WHERE W.CounterID<=" & intCounters & _
    '                               " AND WarnModule='SALE' AND warnBase='SCHEDULE' "
    '        If DbHelper.ExecuteDataTable(strSql).Rows.Count > 0 Then
    '            Return 1
    '        Else : Return 0
    '        End If
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "CheckForSchedule")
    '        Return 0
    '    End Try
    'End Function

    Public Shared Function GetStockIDS(ByVal CounterSeqID As Integer, ByRef dbConnection As dbAccess) As String
        Dim strStocks As New StringBuilder
        Try
            'COMMA SEPAREATED  'StockID,Quantity,SchemeItemQty VALUES
            Dim strVAL As String
            strVAL = dbConnection.ExecuteScalar("SELECT StockID FROM wSaleDet WHERE SeqID=" & CounterSeqID)
            Dim strStockDtls() As String = strVAL.Split(",")

            For Each Value As String In strStockDtls
                If Value = "" Then Exit For
                strStocks.Append(Mid(Value, 1, InStr(Value, "-", ) - 1) & ",")
            Next
            If strStocks.Length > 0 Then strStocks.Replace(",", "", strStocks.Length - 1, 1)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetStockIDS")
            strStocks = Nothing
        Finally
            GetStockIDS = strStocks.ToString()
        End Try
    End Function
#End Region '"GETDETAILS"
    Public Shared Function GetParty() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PartyCode,P.PartyName FROM [NGSPaintsDB].[dbo].Party P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetPromoter() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PromoterCode,P.PromoterName FROM [NGSPaintsDB].[dbo].promoter P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetColors() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[ColorCode],[ColorDescription] FROM [NGSPaintsDB].[dbo].[Color] WHERE [IsDeleted]=0")
    End Function

    Public Shared Function GetColors(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer, ByVal intProductID As Integer) As SqlDataReader
        'Returns The ItemList used for Sale Item List
        Dim StrSql As String = ""
        Try

            'StrSql = "SELECT  mpItem.ItemID,SeqItemID AS  Code,ItemName AS  Name,mfctrShortName AS  Company," & _
            '             " ISNULL((select SUM(Quantity ) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID = mpItem.ItemID  ),0) AS  Stock," & _
            '             " 0 LocationCode,mpItem.minreorderlevel AS  ReorderQty ,ScheduleDrug AS  Schedule,ELStock AS  EmergStock " & _
            '             " FROM mpItem INNER JOIN  mManufacturer ON mManufacturer.mfctrID = mpItem.mfctrID " & _
            '             " ORDER BY "

            'SELECT P.[Id],P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,R.PricePerPack AS Rate 
            StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            ''StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription,S.Quantity FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) INNER JOIN [NGSPaintsDB].[dbo].[tStock] S ON(P.ID= S.ItemID AND C.ID=S.CLRCode) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].[dbo].[Color]"


            'If intColumnIndex = 1 Then
            '    StrSql = StrSql & " ColorCode"
            'Else : StrSql = StrSql & " ColorDescription"
            'End If
            StrSql = StrSql & " ColorDescription"
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))

            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)

            'Call DbHelper.ExecuteDataReader(StrSql, Dtr)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetGoDownList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[GodownCode],[GodownName] FROM [NGSPaintsDB].[dbo].[Godown] WHERE IsDeleted=0")
    End Function

    'Public Shared Function GetSiteList() As DataTable
    '    Return DbHelper.ExecuteDataTable(" SELECT [Id],[SiteCode],[SiteName] FROM [NGSPaintsDB].[dbo].[Site] WHERE [IsDeleted]=0")
    'End Function
    Public Shared Function GetPriceList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [ID],[PRICELISTCODE],[PRICELISTNAME] FROM [NGSPAINTSDB].[DBO].[PRICELIST] WHERE [ISDELETED]=0")
    End Function
    Public Shared Function GetPackList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id] AS PACKID ,([PackingContain] + ' '+ [PackingCode]+ ' '+ [PackingDescription]) AS PACK  FROM [NGSPaintsDB].[dbo].[Packing] WHERE [IsDeleted]=0")

    End Function

    Public Shared Function GetOtherCharges() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT Id,ChargeName,'0' AS Value,0.0 AS Amount,IsTaxable FROM OtherCharge WHERE IsDeleted =0 AND AddToInvoice =1"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetOtherCharges", strSql)
            Return Nothing
        End Try
    End Function
End Class